|
 Wo sind
die Default NLS-Parameter definiert ?
Der Parameter NLS_DATE_FORMAT ist wichtig, wenn
man keine expliziten Datumsmasken verwendet.
Man findet die
NLS-Paramter auf Session, Instance und Database Level wie folgt: SELECT
* FROM nls_session_parameters;
SELECT * FROM
nls_instance_parameters;
SELECT * FROM nls_database_parameters;
PARAMETER
VALUE
------------------------------
----------
NLS_LANGUAGE
AMERICAN
NLS_TERRITORY
AMERICA
NLS_CURRENCY
$
NLS_ISO_CURRENCY
AMERICA
NLS_NUMERIC_CHARACTERS
.,
NLS_DATE_FORMAT
DD-MON-YY
NLS_DATE_LANGUAGE
AMERICAN
NLS_CHARACTERSET
WE8ISO8859
NLS_SORT
BINARY
NLS_CALENDAR
GREGORIAN
NLS_RDBMS_VERSION
7.3.4.4.0
Datum Arithmetic mit
Oracle
Ein oft gestellte Frage im Zusammenhang mit
Oracle lautet: Wie kann man 1 Minute oder 10 Sekunden zu einem gegebenen
Datum addieren oder subtrahieren. Dazu der folgende Ausschnitt aus dem
Oracle SQL Manual:
You can add and subtract number constants as
well as other dates from dates. Oracle interprets number constants in
arithmetic date expressions as numbers of days. For example, SYSDATE + 1
is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten
minutes from now. Subtracting the HIREDATE column of the EMP table from
SYSDATE returns the number of days since each employee was hired. You
cannot multiply or divide DATE values. Oracle provides functions for many
common date operations. For example, the ADD_MONTHS function lets you add
or subtract months from a date. The MONTHS_BETWEEN function returns the
number of months between two dates. The fractional portion of the result
represents that portion of a 31-day month.
Unsere Happy New Year 2000 Function lautet
also:
set serveroutput on;
declare
oldDate DATE;
newDate DATE;
begin
oldDate :=
to_date('31.12.1999:23:59:59','DD.MM.YYYY:HH24:MI:SS');
newDate
:= oldDate + 1/86400;
dbms_output.put_line(
'newDate='
||to_char(newDate,'DD.MM.YYYY:HH24:MI:SS');
end;
/
newDate=01.01.2000:00:00:00
PL/SQL
procedure successfully completed.
How to
implement Sleep Funktion in PL/SQL ?
Gibt es unter Oracle PL/SQL eine SLEEP Funktion
? .... Ja. Die folgende kleine Funktion versucht eine System Ressource zu
erhalten, zB wenn ein Index kreiert werden soll auf einer "Busy" Tabelle.
Man wartet eine gewisse Zeit und versucht es solange bis der Zähler das
Maximum erreicht hat.
CREATE OR REPLACE PROCEDURE TryToGetSlot IS
GotIt BOOLEAN := FALSE;
Count NUMBER :=
0;
BEGIN
WHILE (NOT GotIt AND NOT (Count > 10))
LOOP
BEGIN
-- Try
to get free slot, if OK, set GotIt =
TRUE
-- else EXCEPTION will
automatically fire.
(Insert Code
here)
GotIt :=
TRUE;
EXCEPTION
WHEN OTHERS THEN
GotIt :=
FALSE;
DBMS_LOCK.SLEEP(10);
Count := Count + 1;
END;
END
LOOP;
END;
/
The mistery of
VARCHAR2
As I understand it, industry standard VARCHAR types can
store 'empty' strings, but Oracle currently does not, although it reserves
the right to change the functionality of VARCHAR types to have this
feature. Oracle invented the VARCHAR2 type which is the non-standard
varchar (which changes empty strings to nulls), and
*currently* the VARCHAR is just a synonym for VARCHAR2. Oracle advises
everyone to use VARCHAR2 and not VARCHAR if they wish to maintain backward
compatiblity.
The mistery of Oracle
NULL's
If a column in a row has no value, then column is said to
be null, or to contain a null. Nulls can appear in columns of any datatype
that are not restricted by NOT NULL or PRIMARY KEY integrity constraints.
Use a null when the actual value is not known or when a value would not be
meaningful. Do not use null to represent a value of zero, because they are
not equivalent. (Oracle currently treats a character value with a
length of zero as null. However, this may not continue to be true
in future releases, and Oracle recommends that you do not treat empty
strings the same as NULLs.) Any arithmetic expression containing a null
always evaluates to null. For example, null added to 10 is null. In fact,
all operators (except concatenation) return null when given a null
operand. Oracle is usually careful to maintain backward compatibility, so
I'd be slightly surprised to see this change even in Oracle 9 and very
surprised to see it change in any version of Oracle 8.
The mistery of
ROWNUM and ORDER BY
From the Oracle7 Server SQL Reference Manual: ROWNUM
Oracle7 assigns a ROWNUM value to each row as it is
retrieved, before rows are sorted for an ORDER BY clause,
so an ORDER BY clause normally does not affect the ROWNUM of each row.
However, if an ORDER BY clause causes Oracle7 to use an index to access
the data, Oracle7 may retrieve the rows in a different order than without
the index, so the ROWNUMs may differ than without the ORDER BY clause.
You can use ROWNUM to limit the number of rows
returned by a query, as in this example:
SELECT * FROM emp WHERE ROWNUM < 10;
You can also use ROWNUM to assign unique values to
each row of a table, as in this example:
UPDATE tabx SET col1 = ROWNUM;
Note that conditions testing for ROWNUM values
greater than a positive integer are always false.
For example, this query returns no rows:
SELECT * FROM emp WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes
the condition false. The second row
to be fetched is now the first row
and is also assigned a ROWNUM of 1 and makes the condition
false. All
rows subsequently fail to satisfy the condition, so no rows are
returned.
Can
I replicate only certain partitions of a partitioned table ?
If so, how ? ..... Solution Summary: YOU HAVE TO REPLICATE
THE ENTIRE TABLE
Solution Description: Oracle8 Server Replication:
Appendix A: New Features:
"Oracle8 supports the replication of
partitioned tables and indexes. ...."
Chapter: Administering A
Replicated Environment Partitioned Tables and Indexes:
"With
masters, you must still replicate the entire table; you cannot replicate a
partition. If you want to replicate selected paritions only rather than
the entire table use updatable snapshots."
Workaround: Create a
Snapshot withe a WHERE Clause, which SELECT's only from one Partition.
How to set
«seminfo_semmni» on Sun-Solaris / Oracle ?
Nach einer Hardware Migration unter SUN-Solaris / Oracle
konnte die grosse Datenbank eines Kunden nicht mehr gestartet werden.
Kernelparameter werden unter Sun-Solaris in /etc/system
eingestellt. Besonders wichtig im Zusammenhang sind Parameter, welche die
Anzahl Semaphoren und die Grösse des Shared Memory festlegen:
set semsys:seminfo_semmni=400
Diese Zeile limitiert die "Number of semaphore
identifiers". Der Wert muss mindestens so gross wie PROCESSES in
initSID.ora sein. Bei mehreren DB's die Summe aller PROCESSES. Nach der
Änderung dieses Kernelparameters muss ein System-Reboot durchgeführt
werden.
Help with
SQL -- aus unserem Support Alltag
Wie kann ich folgende zwei SQL Statements in einem
zusammenfassen ?
SELECT ms_id
FROM msisdn
WHERE msisdn =
'799990001';
INSERT INTO onp (onp_id, ms_id, request)
VALUES (1,
$ms_id, 'x1');
Beispielsweise etwa so ?
INSERT INTO ONP (onp_id, ms_id, request)
VALUES
(1,(SELECT ms_id FROM msisdn WHERE msisdn='799990001'),'x1');
Die selektierten Werte eines SQL SELECT's müssen nicht
zwingend aus einer Tabelle stammen wie die folgende einfache Lösung
demonstriert:
INSERT INTO onp (onp_id,ms_id,request)
SELECT
1,ms_id,'x1'
FROM msisdn
WHERE msisdn = '799990001';
Geändertes
UPDATE Verhalten ab Oracle 8i
Sie möchten alle Werte eines Attributes einer Tabelle mit
einem Wert aus einer anderen Tabelle erhöhen, oder etwas "mathematischer"
ausgedrückt:
TABLE1.VALUE1 = TABLE1.VALUE1 + TABLE2.VALUE2 über alle
Rows von TABLE1.
CREATE TABLE booking (
acc_id NUMBER(15) NOT
NULL,
amount NUMBER(15) NOT NULL
);
CREATE TABLE account
(
acc_id NUMBER(15) NOT NULL,
balance NUMBER(15) NOT
NULL
);
INSERT INTO account VALUES (1,100);
INSERT INTO
account VALUES (2,200);
INSERT INTO account VALUES (3,300);
INSERT
INTO account VALUES (4,400);
INSERT INTO account VALUES
(5,500);
INSERT INTO booking VALUES (1,10);
INSERT INTO booking
VALUES (2,20);
INSERT INTO booking VALUES (3,30);
INSERT INTO
booking VALUES (4,40);
INSERT INTO booking VALUES
(5,50);
COMMIT;
UPDATE account A
SET balance = balance + (SELECT
amount
FROM booking
B
WHERE B.acc_id = A.acc_id)
WHERE EXISTS (SELECT
'X'
FROM booking
B
WHERE B.acc_id = A.acc_id);
ERROR at line 2:
ORA-00936: missing expression
UPDATE account A
SET A.balance = (SELECT B.amount +
A.balance
FROM booking
B
WHERE B.acc_id = A.acc_id)
WHERE EXISTS (SELECT
'X'
FROM booking
B
WHERE B.acc_id = A.acc_id);
ACC_ID BALANCE
------
-------
1
110
2
220
3
330
4
440
5 550
Wie
findet man den maximalen Wert aus einer Gruppe ?
Diese simple Aufgabenstellung wird häufig an
uns gestellt, da sie offenbar auf den ersten Blick einfach erscheint, dann
aber meistens zu falschen Ergebnissen führt. Wir möchten diesen
Sachverhalt an einem typischen Beispiel erläutern.
SELECT acq_id
"AcqID",
TO_CHAR(date_sent,'DD.MM.YYYY:HH24:MI:SS') "DateSent"
FROM
ccrsettlement
WHERE date_sent IS NOT NULL
ORDER BY acq_id,date_sent
DESC;
AcqID DateSent
-----
-------------------
1
02.08.1999:22:55:40
1
29.07.1999:12:17:07
2 09.08.1999:07:39:31
<-- Man möchte diesen Wert selektieren
2
09.08.1999:07:25:24
2
24.07.1999:13:53:55
3
19.08.1999:05:53:05
3
02.08.1999:13:58:02
3
02.08.1999:13:52:19
3 02.08.1999:13:42:02
Man ist versucht, als ersten Ansatz das
folgende Query zu verwenden, das aber nicht das gewünschte Resultat
liefert.
SELECT acq_id
"AcqID",
TO_CHAR(date_sent,'DD.MM.YYYY:HH24:MI:SS') "DateSent"
FROM
ccrsettlement
WHERE acq_id = 2
AND date_sent =
GREATEST(date_sent);
AcqID DateSent
---------
-------------------
2
09.08.1999:07:25:24
2
09.08.1999:07:39:31
2
24.07.1999:13:53:55
Mittels folgendem Subquery kann der maximale Wert einer
Gruppe herausgefunden werden. Besonders interessant dabei ist, dass man
auch alle Attribute selektieren kann, da die
WHERE und GROUP BY Klausel
im Subquery untergebracht ist. Dies ist oft ein Problem bei Queries der
Form SELECT * FROM ... GROUP BY ..., da hier die selektieren Attribute mit
den gruppierten Attributen übereinstimmen müssen (ORA-00979: not a GROUP
BY expression).
SELECT acq_id
"AcqID",
TO_CHAR(date_sent,'DD.MM.YYYY:HH24:MI:SS') "DateSent"
FROM
ccrsettlement
WHERE (acq_id,date_sent) IN (SELECT
acq_id,max(date_sent)
FROM
ccrsettlement
WHERE acq_id =
2
GROUP BY acq_id);
AcqID DateSent
---------
-------------------
2
09.08.1999:07:39:31
oder alle Attribute selektieren
SELECT *
FROM ccrsettlement
WHERE
(acq_id,date_sent) IN (SELECT
acq_id,max(date_sent)
FROM
ccrsettlement
WHERE
acq_id =
2
GROUP
BY acq_id);
How to create a
read-only table ?
Oracle-7 und 8 ermöglicht es, Tablespaces als «Read-Only» zu
definieren. Manchmal möchte man jedoch nur eine einzelne Tabelle mit
diesem Attribut versehen, dies natürlich für alle Applikationen.
CREATE TRIGGER tab_readonly
BEFORE DELETE OR INSERT OR
UPDATE
ON emp
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20201, 'Table Status: READ ONLY.');
END;
How to remove
«in-doubt» transactions ?
In doubt transactions may occur at double phase commit
time for network, break node ... reason. Normally if a double phase commit
failed, you may have some entries in views DBA_2PC_PENDING and
DBA_2PC_NEIGHBORS. To force the in doubt transaction to rollback you have
to use the command:
ROLLBACK FORCE <transaction_id>;
Unfortunately, sometime the entries are still there ...
and you may discover in your alert<sid>.log file something like:
ora-600 [18104] ... This point to bug 445604 Fixes in version Oracle
7.3.4
Now it's possible to use package DBMS_TRANSACTION to solve
the problem if rollback force do not clean all entries.
Do as follow:
EXECUTE
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('xid');
Where 'xid' come from:
SELECT local_tran_id FROM DBA_2PC_PENDING;
Multi-Threaded Server
(Average Wait Time)
If wait time gets to be excessive, then more dispatchers need to be
activated. If the value increases as the number of concurrent users
increases, increase the number of dispatchers. The query to determine this
is.
COLUMN wait_time FORMAT 99999 heading "Average
Wait"
COLUMN protocol FORMAT A60 heading "Protocol"
SELECT network
protocol,
DECODE (sum(totalq),0,'
No responses',
TO_CHAR(sum(wait)/sum(totalq)*100,'9.99')
||
' Sec Wait Per Response')
wait_time
FROM v$queue q, v$dispatcher d
WHERE q.type =
'DISPATCHER'
AND q.paddr = d.paddr
GROUP BY
network;
This query should be run multiple times daily during both light and
heavy usage.
Force a rollback
segment offline
If you are using public rollback segments it may be
difficult to force a rollback segment offline and keep it there, unless
you use the following INIT.ORA paramater. This is useful when a rollback
segment has been corrupted or must be dropped and rebuild.
_offline_rollback_segments = <RBS-name>
Anzeige des
letzten Checkpoints in den File Headern
select FILE#
file_nr,
to_char(CHECKPOINT_TIME,'DD.MM.YYYY:HH24:MI:SS')
checkpoint_time,
name
file_name
from
v$datafile_header;
Checkpoint
File# Time
Filename
------- --------------------
--------------------------------------
1 17.05.1999:11:29:56
/opt/oradata/RAB1/sys/RAB1_sys1.dbf
2 17.05.1999:11:29:56
/opt/oradata/RAB1/rbs/RAB1_rbs1.dbf
3 17.05.1999:11:29:56
/opt/oradata/RAB1/tmp/RAB1_temp1.dbf
Anzahl Query
Reparsings im Shared Pool
Wie viele Queries müssen neu geparst werden im Vergleich
zu den total Ausgeführten ?
select sum(pins) "SQL
Executions",
sum(reloads)
"Necessary SQL Reparsing"
from v$librarycache;
Executions Necessary SQL Reparsing
----------
-----------------------
3436
9
Freier Platz im
Shared Pool anzeigen
select * from v$sgastat
where name = 'free
memory';
Hints und
ANALYZE für Bitmap Index Zugriff
Damit ein Bitmap Index Zugriff vom COST based Optimizer
favorisiert und ausgeführt wird müssen die Optimizer Statistiken korrekt
aufgesetzt sein. Es reicht jedoch nicht aus, nur das entsprechende Schema
einem ANALYZE zu unterziehen, es muss auf das entsprechende Table-Column
ausgeweitet werden. Der Hint muss (wie im Tuningguide beschrieben) mit der
exakten Tabellenbezeichnung gemacht werden, d.h. wenn ein Tablealias
angewendet wird, MUSS im Hint auch die Tabelle mit dem Alias
referenziert werden.
ANALYZE für Bitmap Index Zugriff auf dem Column STATUS
ANALYZE INDEX bitmap_index ESTIMATE
STATISTICS;
ANALYZE TABLE xyz ESTIMATE STATISTICS FOR COLUMNS
STATUS;
Angabe des Hint
SELECT /*+ INDEX(tab_alias bitmap_index) */
......,
How to
set date format for best Y2k Compliance ?
Oracle suggest the date format
NLS_DATE_FORMAT='YY-MON-RR' in order to overwrite the
default of 'YY-MON-YY'. This way, any program that was
developed assuming the default date format of 'YY-MON-YY', will work
properly. To facilitate year 2000 compliance for applications that use the
two-digit year format the Oracle7 Server and Oracle8 Server provides a
special year
format mask 'RR'.
The Oracle RDBMS has always stored dates using a
four-digit year (the ‘OraDate’ format), hence customers using the DATE
data type should not have any application level problems. To facilitate
year 2000 compliance for applications that use the two-digit year format
the Oracle7 Server and Oracle8 Server provides a special year
format mask ‘RR’. Using the ‘RR’ format, any two-digit year
entered will be converted thus:
|
|
|
|
|
0-49 |
0-49 |
Current Century |
|
50-99 |
0-49 |
One Century after current |
|
0-49 |
50-99 |
One Century before current |
|
50-99 |
50-99 |
Current Century |
Therefore, regardless of the current century at the time the data is
entered the 'RR' format will ensure that the year stored in the database
is as follows:
If the current year is in the second half of the century (50 - 99)
- and a two-digit year between ‘00’ and ‘49’ is entered: This will be
stored as a next century year.
E.g. ‘02’ entered in 1996 will be
stored as ‘2002’.
- and a two-digit year between ‘50’ and ‘99’ is entered: This will be
stored as a current century year.
E.g. ‘97’ entered in 1996 will be
stored as ‘1997’.
If the current year is in the first half of the century (00 - 49)
- and a two-digit year between ‘00’ and ‘49’ is entered: This will be
stored as a current century year.
E.g. ‘02’ entered in 2001 will be
stored as ‘2002’.
- and a two-digit year between ‘50’ and ‘99’ is entered: This will be
stored as a previous century year.
E.g. ‘97’ entered in 2001 will be
stored as ‘1997’.
The ‘RR’ date format is available for inserting and updating DATE data
in the database. It is not required for retrieval/query of data already
stored in the database as Oracle has always stored the YEAR component of a
date in it’s four-digit form.
How to protect
PL/SQL Source Code ?
PL/SQL V-2.2, available with Oracle 7.2, implements a
binary wrapper for PL/SQL to protect the source code. This is done via a
standalone utility that transforms the PL/SQL source code into portable
binary object code (somewhat larger than the original). This way you can
distribute software without having worry about exposing your proprietary
algorithms and methods. SQLPLUS and SVRMGRL will still understand and know
to execute such scripts. Just be careful, there is no "decode" command
available, the syntax is:
wrap iname=myplsqlsource.sql
oname=myplsqlsource.plb (UNIX)
wrap80 iname=myplsqlsource.sql
oname=myplsqlsource.plb (NT)
How to
switch to another Orcale User as DBA ?
Many batch jobs must change a user password to a "dummy"
password to login as this user temporarilly. The problem is always the
encrypted password which cannot be de-crypted so that the password can be
reset to the original. There is a way to do this, using the VALUES
keyword, this is exactly what IMPORT does when it must create users. We
show you how to perform this task using the famous account of SCOTT/TIGER.
If a DBA doesn't know the password "TIGER", he can temporarilly change
this password to another password, logon as user SCOTT, and set the
password back after the jobs as user SCOTT is done. Follow the
instructions below:
sqlplus system/manager
select password from
dba_users where username =
'SCOTT';
PASSWORD
------------------------------
F894844C34402B67
alter user scott identified by
hello;
sqlplus scott/hello
alter user SCOTT identified by VALUES
'F894844C34402B67';
Show object sizes in
the database
It's often very interesting to know the size of each
object in the database. For example if you need to pin a package, trigger
in the memory. Note, that NOT the size of a table including the rows will
be shown, but the size of the table itself.
Object
Source Parsed
Code
Owner Name
Type
Size Size
Size
-------- --------------------- -------------
--------- --------- ---------
PPB
CDR_TOOL
PACKAGE
788
388
107
PPB CDR_TOOL
PACKAGE BODY 2078
0 1736
PPB
CLEANUP_PARTIAL_CDR PROCEDURE
3382
6296
4790
PPB CDR
TABLE
0
2091
0
PPB TRG_CLT_MSG
TRIGGER
0
0 453
Show
Source
Viewing
The SQL Statements of the CPU-Intensive Oracle Processes
First get the top 10 CPU-intensive Oracle processes on the
operating system with the first column giving the %CPU used, the second
column unix PID, the third column USER , the fourth column TERMINAL, and
the last column Unix PROCESS (works only for UNIX).
ps -eaf -o pcpu,pid,user,tty,comm | grep ora
|
grep -v \/sh | grep -v ora_ | sort -r | head
-20
Now you can specify the found PID in the following
SQL-Statement:
column username format a9
column sql_text format
a70
SELECT a.username, b.sql_text
FROM v$session a,
v$sqlarea b, v$process c
WHERE (c.spid = '&PID' OR a.process =
'&PID')
AND a.paddr = c.addr
AND
a.sql_address = b.address
/
How to find
out which object is corrupted ?
During a SELECT statement, we get the error:
ORA-1578: file#1 block#3840 corrupted Oracle block.
It is telling us, that the corruption took place in
file#1. This always a SYSTEM tablespace file. This may
mean that the corrption problem may only be resolved with the recreation
of the database follwed by a full database import. Only if this is the
case, do a full database export with the database unavailable to users
immediately followed by the database recreation and import in order to
prevent loss of data. Now how to find out exactly which object is
corrupted. It may be a rollback segment or an index that can simply be
recreated. Let's see how we can find out this.
Connect to an DBA account:
SELECT segment_type, segment_name
FROM
dba_extents
WHERE file_id = 1
AND block_id <
3840
AND block_id + blocks >=
3840;
SEGMENT_TYPE
SEGMENT_NAME
-----------------
----------------------------------------
INDEX
I_SOURCE1
or use:
SELECT segment_type, segment_name
FROM
dba_extents
WHERE file_id = 1
AND 3840 BETWEEN block_id
AND block_id + blocks
-1;
SEGMENT_TYPE
SEGMENT_NAME
-----------------
----------------------------------------
INDEX
I_SOURCE1
This is a simulated example, altough file#1 ALWAYS belongs
to the SYSTEM tablespace, block #3840 could be anything. Lower block
numbers in the #1 file are likely bootstrap segments. In this case
I_SOURCE is corrupted, this is a SYS Index that cannot be dropped. In this
case only recreating the database will resolve the problem. If it is a
table it must be understood that the data within the corrupted blocks is
lost. You can try to save as much of the data in the corrupted table
before the bad block and create a new table around the corrupted part. Get
the rowid's around the corrupted block with:
SELECT ROWIDTOCHAR(rowid) FROM <table-name>;
How
to create an empty copy of an existing table ?
CREATE TABLE new_table AS
SELECT * FROM
old_table
WHERE 1 = 2;
Table created.
How to
remove duplicate rows from a table ?
SQL> select * from
address;
NAME VORNAME
BIRTH
-------- -------- ----
zahn
martin 1954
zahn martin
1954
hodler kurt
1962
wyss bruno
1965
Get the Duplicates with:
SELECT name,vorname FROM
address
GROUP BY name,vorname
HAVING COUNT(*) >
1;
NAME VORNAME
--------
--------
zahn martin
Delete the
Duplicates with
DELETE from address A
WHERE (A.name,
A.vorname, A.birth) IN
(SELECT B.name, B.vorname,
B.birth FROM address B
WHERE A.name =
B.name AND A.vorname =
B.vorname
AND A.birth =
B.birth AND A.rowid > B.rowid);
1 row deleted.
How
to create a new Control file, how to document the Database ?
Connect as SYSTEM or SYS and do:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SELECT *
FROM v$dbfile;
SELECT * FROM v$logfile;
The TRACE File for the
Controlfile can be found in the directory defined by the init.ora
Parameter: core_dump_dest.
How
to enable / disable Archiving (Online-Backup)
Check that the following
parameters are set in init<SID>.ora:
LOG_ARCHIVE_DEST
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_START
= TRUE/FALSE
Enable or disable Archiving
with
svrmgrl
connect internal oder connect <user> as
SYSDBA
startup mount;
archive log list;
Database log mode No Archive Mode
Automatic archival
Disabled
Archive destination
/oracle/v805/oradata/arc
Oldest online log sequence
340
Current log sequence 341
alter system archive
log start / stop;
archive log list;
Database
log mode No Archive Mode
Automatic archival
Enabled
Archive destination
/oracle/v805/oradata/arc
Oldest online log sequence
340
Current log sequence 341
alter database
archivelog / noarchivelog;
archive log list;
Database log mode Archive Mode
Automatic archival
Enabled
Archive destination
/oracle/v805/oradata/arc
Oldest online log sequence
340
Next log sequence to archive 341
Current log sequence 341
alter database open;
Manually archives the current
log file group, forcing a log switch
alter system archive log current;
Manually archives all full
unarchived redo log file groups
alter system archive log all;
How
many valid / invalid objects exists owned by this oracle user ?
Often we should know, how many valid and invalid objects
an oracle user ows. Specially if we compare a schema on two different
databases.
SELECT DISTINCT (object_type) object, status,
COUNT(*)
FROM user_objects
GROUP BY object_type,
status;
OBJECT
STATUS COUNT(*)
------------- -------
---------
INDEX VALID
95
PACKAGE INVALID
1
PACKAGE BODY
INVALID
1
PROCEDURE INVALID
1
PROCEDURE VALID
2
SEQUENCE VALID
20
TABLE VALID
63
TRIGGER INVALID
3
TRIGGER VALID
1
How
many extents are used by a rollback segment
If you are running large transactions it is important to
have enough rollback segment space. To check the current allocated number
of extents and the max number of extens in a rollback segment, use the
following query:
SELECT COUNT(*) FROM dba_extents
WHERE
segment_name = 'SYSTEM'
AND segment_type =
'ROLLBACK';
COUNT(*)
---------
4
SELECT max_extents
FROM dba_rollback_segs
WHERE segment_name =
'SYSTEM';
MAX_EXTENTS
-----------
249
DBA_EXTENTS: This view lists the extents comprising all
segments.
V$ROLLSTAT: This view contains segment
statistics.
DBA_ROLLBACK_SEGS: This view contains descriptions of the
rollback segments.
Startup with a
corrupted Redo-Log
There may be situations where you need to startup with a
corrupted redo log. In this case there is an init.ora parameter that
allows this: _ALLOW_RESETLOGS_CORRUPTED=TRUE. However, this will
startup even if it results in corrupted data, so only use it if you have
to and only to startup long enough to get an export of critical data. Use
only under the guidance of Oracle Support.
Cache a Table in the
SGA
In order to cache a table in the SGA it has to be smaller
than CACHE_SIZE_THRESHOLD as set in the init.ora file. However, the cost
based analyzer doesn't take table cacheing into account when doing
optimization so you may want to force the table using hints.
Increase Speed with Large
Imports
For large imports, import with INDEXES=N
and build your indexes seperately to increase import speed.
Tracking and
validating objects
Oracle can use event setpoints in the initialization file
for tracking and validation of objects. For example:
event="10210 trace name context forever, level 10" sets
integrity checking for tables
event="10211 trace name context forever,
level 10" sets integrity checking for indexes
event="10231 trace name
context forever, level 10" tells a full table scan to skip any
corrupted blocks
The last event setting (10231) will allow export
to extract all data, except the corrupted block, from a table.
You should only use these under guidance from Oracle support.
Read Consistence
Garantiert die konsistente Sicht der Daten, während des
gleichzeitigen Schreibens von Transaktionen durch andere Sessions.
SET TRANSACTION READ ONLY;
SELECT ....
SELECT
....
SELECT ....
COMMIT oder ROLLBACK;
Die drei SELECT's sind lesekonsistent, das heisst man
sieht die Daten wie sie zum Zeitpunkt des Kommandos SET TRANSACTION READ
ONLY waren. In PL/SQL kann die Funktion DBMS_TRANSACTION.READ_ONLY;
verwendet werden.
UNRECOVERABLE unter
Oracle-7
Bei folgenden Statements möglich:
CREATE TABLE xxx AS SELECT * FROM xxx
UNRECOVERABLE;
CREATE INDEX xxx ON xxx(xxx) UNRECOVERABLE;
ALTER
INDEX xxx REBUILD UNRECOVERABLE;
SQL-Statements im Shared
Pool
|
SELECT sql_text, executions,
first_load_time, invalidations
FROM v$sqlarea; |
Aktuelle Befehle in der SGA |
|
SELECT COUNT(*) FROM v$sqlarea; |
Anzahl Befehle in der SGA |
|
SELECT sql_text FROM v$sqlarea
WHERE
users_executing > 0; |
Gerade in Ausführung |
|
ALTER SYSTEM FLUSH SHARED POOL; |
Alle SQL-Statements aus dem Shared Pool entfernen,
schafft Platz für grosses Statement. |
Wait
-Statistiken auf Datenbuffer, Rollback-Segmenten
SELECT * FROM v$waitstat;
TIMED_STATISTICS = TRUE muss
gesetzt sein, zB mit
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
Verfügbare
V$ und X$ Tabellen, Definierte Views mit SQL-Text
SELECT * FROM v$fixed_table;
SELECT * FROM
v$fixed_view_definition;
User mit offenen
Transaktionen
SELECT username, machine, terminal, program
FROM
v$session, v$transaction
WHERE addr = taddr;
Cachen von Tabellen im
Memory
Normalerweise werden Blöcke, welche über einen
Fulltable-Scan gelesen werden nicht in der SGA gehalten, sondern gleich
wieder entfernt. Dies kann für "Lookup-Tabellen" verhindert werden, alle
Rows bleiben dann auch nach einem Fulltablesan in der SGA.
ALTER TABLE emp CACHE;
ALTER TABLE emp NOCACHE;
Empfehlungen zu
Storage Parametern
- PCTFREE und PCTUSED müssen zusammen weniger als 100%
ergeben
- PCTFREE für normale UPDATE-Aktivitäten auf 20% setzen. Wenn
keine UPDATEs stattfinden kleiner.
- PCTINCREASE immer 0 wählen.
Index
in anderen Tablespace verlegen während des laufenden Betriebes
ALTER INDEX xxx REBUILD
STORAGE (INITIAL 100K NEXT
100K PCTINCREASE 0)
PCTFREE 5
TABLESPCAE newts;
Free Space in
Tablespaces
Der freie Platz und dessen Fragmentierung kann im Oracle
Tablespace Manager oder mit einem einfachen SQL-Script angezeigt werden.
Klicken Sie hier um den
Source-Code zu sehen.
Coalesce von Free
Space
Wenn für eine Tabelle ein Extent benötigt wird, so sucht
Oracle die Liste der freien Extents ab. Die erste ausreichend grosse Lücke
wird alloziert. Wird keine ausreichend grosse Lücke gefunden, so wird die
Liste nochmals abgesucht, ob es benachbarte freie Lücken gibt, welche
zsammen ausreichend gross sind. Falls noch immer keine ausreichend grosse
Lücke gefunden werden kann, so wird die Fehlermeldung "failed to
allocate extent of size <bytes> in tablespace
<tablespace>" generiert (falls nicht ein Datafile mit
AUTOEXTEND vergrössert werden kann).
Zusammenhängende freie Lücken können zusammengefügt werden
mittels:
ALTER TABLESPACE xxx COALESCE;
Stored Procedure für alle Tablespaces:
CREATE OR REPLACE PROCEDURE coats
IS
vCursor INTEGER :=
DBMS_SQL.Open_Cursor;
vDummy
INTEGER;
BEGIN
FOR tsrec IN (SELECT
tablespace_name,
TO_CHAR(percent_blocks_coalesced,'990.00')
percent_blocks_coalesced
FROM
dba_free_space_coalesced
WHERE
percent_blocks_coalesced < 100) LOOP
DBMS_SQL.Parse(vCursor,'ALTER TABLESPACE
'||tsrec.tablespace_name
||' coalesce',
DBMS_SQL.Native);
vDummy :=
DBMS_SQL.Execute(vCursor);
dbms_output.put('Tablespace ');
dbms_output.put(RPAD(tsrec.tablespace_name,30,'.'));
dbms_output.put(': ');
dbms_output.put(tsrec.percent_blocks_coalesced);
dbms_output.new_line;
END LOOP;
DBMS_SQL.Close_Cursor(vCursor);
END;
/
Identifikation
von kritischen Tabellen und Indexes
Wenn der Platz in einem Tablespace knapp ist können
Tabellen oder Indexes keine weiteren Extents allozieren. Dies kann auch
eintreten, wenn die Extents zu gross spezifiziert wurden oder PCTINCREASE
grösser als 0 gewählt wurde. Man kann auf einfache Weise testen, ob ein
weiterer Extent alloziert werden kann.
ALTER TABLE emp ALLOCATE EXTENT;
ALTER TABLE emp
DEALLOCATE UNUSED;
|
SQL-Script (V7)
SQL-Script (V8) |
Zeigt Tabellen, welche kein Extent mehr allozieren
könnten, also als kritisch einzustufen sind |
|
SQL-Script (V7)
SQL-Script (V8) |
Zeigt Statistiken zu Füllungsgrad einer Tabelle.
Insbesondere kann verifiziert werden wie viele Rows noch
eingefügt werden können ohne einen zusätzlichen Extent zu
allozieren. |
|
SQL-Script (V7)
SQL-Script (V8) |
Zeigt Indizes, welche keine weiteren Extents mehr
allozieren könnten, also als kritisch einzustufen
sind |
Zuordnung
einer Transaktion zu einem Rollback-Segment
Zur Erinnerung sei hier nochmals darauf hingewiesen
dass:
- Eine Transaktion kann nur ein Rollback-Segment
verwenden
- Ein Rollback-Segment kann von mehreren Transaktionen
benutzt werden.
Für lang dauernde Transaktionen ist es oft ein Vorteil ein
spezielles Rollback-Segment zu erstellen und dieses der Transaktion
zuzuweisen.
SET TRANSACTION USE ROLLBACK SEGMENT rbs01;
/* SQL*Plus */
DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT ('rbs01');
/* PL/SQL */
Mit COMMIT oder ROLLBACK wird die Zuweisung wieder
aufgehoben.
Undokumentierte
INIT.ORA Parameter
Mit dem folgenden Query können unter dem User SYS die
undokumentierten INIT.ORA Parameter abgefagt werden:
set pages 100
col "Parameter" format a40 trunc
col
"Session Value" format a17 trunc
col "Instance Value" format a17
trunc
select a.ksppinm
"Parameter",
b.ksppstvl "Session
Value",
c.ksppstvl "Instance
Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx =
b.indx and a.indx = c.indx
and
substr(ksppinm,1,1)='_'
order by a.ksppinm
/
Wichtiges zu
Referential Integrity und Constraints
Constraints immer mit einem Namen versehen, da ansonsten
nichtssagende Namen (SYS_cnnnn) in Fehlermeldungen erscheinen. Ein
Debugging wird dadurch erschwert.
|
Default
Constraint |
|
CREATE TABLE order
(ord_id NUMBER
NOT NULL,
ord_date DATE DEFAULT SYSDATE); |
Ergänzt bei INSERTS ein Attribut mit einem
Default-Wert, falls das INSERT keinen Wert definiert. Sequences
können nicht als Default-Werte verwendet werden. Grundsätzlich
sollten Default-Werte mit DB-Trigger implementiert werden. |
|
NOT NULL
Constraint |
|
CREATE TABLE order
(ord_id
NUMBER
CONSTRAINT nn_ord_id NOT
NULL,
ord_date DATE DEFAULT SYSDATE); |
Überprüft, dass ein Attribut einen Wert
hat. |
|
Nicht verwenden:
CREATE TABLE mm_order
(ord_id
NUMBER
CONSTRAINT nn_ord_id
CHECK
(ord_id IS NOT NULL),
ord_date DATE DEFAULT
SYSDATE); |
NOT NULL Constraints sollten nicht mit dem
Check-Constraint definiert werden, da NOT NULL mit DESCRIBE nicht
angezeigt wird (siehe Beispiel). |
|
CHECK
Constraint |
|
CREATE TABLE order
(ord_id
NUMBER
CONSTRAINT ck_ord_id
CHECK
ord_id IN (0,1,2,3),
ord_date DATE DEFAULT SYSDATE); |
Check Constraints dienen vorallem zum Prüfen von
Ranges und Domains. Es können keine Subqueries verwendet werden.
Andere Attribute können angesprochen werden, zB CONSTRAINT ck_sal
CHECK (NVL(sal,0) < com); |
|
UNIQUE
Constraint |
|
Default Index (nicht verwenden)
CREATE TABLE order
(...
ord_name VARCHAR2(20)
CONSTRAINT un_ord
UNIQUE,
...); |
Prüfen, dass ein Attribut eindeutig ist, das
Attribut darf NULL Werte haben. UNIQUE Attribute können von Foerign
Keys referenziert werden. Beim Erstellen des Default Index (Beispiel
1) wird automatisch ein UNIQUE Index angelegt. Disable und Drop
Constreint löschen den Index. Ein UNIQUE Constraint kann auch aus
mehreren Attributen bestehen. |
|
Constraint und Index getrennt erstellen
CREATE UNIQUE INDEX idx_empno
ON
emp(empno)
STORAGE (INITIAL 50K NEXT 50K)
TABLESPACE tab;
ALTER TABLE emp
ADD CONSTRAINT
idx_empno
UNIQUE (empno); |
Der unique Index und das Constraint können getrennt
voneinander erstellt werden. Es ist dabei zu beachten, dass der
Indexname und Constraintname identisch sind, im Beispiel idx_ename.
Damit können Probleme beim EXP/IMP vermieden werden. Indexe sollten
wenn möglich in einem anderen Tablespace als die Tabelle angelegt
werden. |
|
Verwenden von USING INDEX
CREATE TABLE order
(...
ord_name VARCHAR2(20)
CONSTRAINT
un_ord
UNIQUE USING INDEX
STORAGE
(INITIAL 50K NEXT 50K)
TABLESPACE
tab,
...); |
Dies ist die bevorzugte Methode um ein unique
Attribut zu erstellen mit der Angabe des unique Index und dessen
Storage Parameter. |
|
PRIMARY Key
Constraint |
|
Default Index (nicht verwenden)
CREATE TABLE order
(...
ord_name VARCHAR2(20)
CONSTRAINT un_ord PRIMARY
KEY,
...); |
Das PRIMARY KEY Constraint verhält sich sehr ähnlich
wie das UNIQUE Constraint. Im Gegensatz zu diesem darf ein Primary
Key aber keine NULL-Werte haben. Ein Primary Key kann auch aus
mehreren Attributen bestehen. |
|
Constraint und Index getrennt erstellen
CREATE UNIQUE INDEX idx_empno
ON
emp(empno)
STORAGE (INITIAL 50K NEXT 50K)
TABLESPACE tab;
ALTER TABLE emp
ADD CONSTRAINT
idx_empno
PRIMARY KEY (empno); |
Der unique Index und das Constraint können getrennt
voneinander erstellt werden. Es ist dabei zu beachten, dass der
Indexname und Constraintname identisch sind, im Beispiel idx_ename.
Damit können Probleme beim EXP/IMP vermieden werden. Indexe sollten
wenn möglich in einem anderen Tablespace als die Tabelle angelegt
werden. |
|
Verwenden von USING INDEX
CREATE TABLE order
(...
ord_name VARCHAR2(20)
CONSTRAINT
un_ord
PRIMARY KEY USING INDEX
STORAGE (INITIAL 50K NEXT 50K)
TABLESPACE
tab,
...); |
Dies ist die bevorzugte Methode um ein Primary Key
zu erstellen mit der Angabe des unique Index und dessen Storage
Parameter. |
|
FOREIGN Key
Constraint |
|
CREATE TABLE emp
(empno NUMBER(5) NOT
NULL,
ename VARCHAR2(30),
deptno
NUMBER(2) NOT NULL
CONSTRAINT
fk_depno
REFERENCES dept(deptno)); |
Sicherstellen der Master-Detail Beziehung |
|
ON DELETE CASCADE
CREATE TABLE emp
(empno NUMBER(5) NOT
NULL,
ename VARCHAR2(30),
deptno
NUMBER(2) NOT NULL
CONSTRAINT
fk_depno
REFERENCES dept(deptno)
ON
DELETE CASCADE); |
Mittels ON DELETE CASCADE wird auf der Foreign Key
Seite angegeben, dass beim Löschen eines Master-Records auch alle
Child-Records gelöscht werden sollen. |
|
Administration von
Constraints |
|
ALTER TABLE emp DISABLE PRIMARY KEY; |
Entspricht einem DROP INDEX |
|
ALTER TABLE dept DISABLE PRIMARY KEY
CASCADE; |
Um einen Primary Key oder ein Unique Constraint,
welches Teil des Foreign Keys sind zu disablen. |
|
ALTER TABLE emp ENABLE PRIMARY KEY
USING INDEX
STORAGE
(INITIAL 100K NEXT 100K)
TABLESPCA tab; |
Erstellen eines Primary Key an bestehender
Tabelle. |
|
ALTER TABLE emp DROP PRIMARY KEY;
ALTER TABLE
emp DROP CONSTRAINT emp_pk; |
Löschen eines Constraints |
|
ALTER INDEX emp_pk REBUILD
UNRECOVERABLE
PARALLEL (DEGREE 2)
TABLESPACE
tab
PCTFREE 5
STORAGE (INITIAL 1M
NEXT
500K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0); |
Rebuild eines Indexes während dem laufenden
Betrieb. |
|
Anzeigen von
inkonsistenten Daten |
|
Ein ENABLE Constraint lockt die gesamte Tabelle, bis
die Constraints aktiviert sind. Falls inkonsistente Daten vorhanden
sind, so wird das Constraint nicht aktiviert. Inkonsistente Daten
werden in die Exceptions-Tabelle EXCEPTIONS abgespeichert. Diese
Tabelle muss mit dem Script utlexcpt.sql
(Oracle-Installation) erstellt werden.
ALTER TABLE dept ENABLE PRIMARY KEY
EXCEPTIONS
INTO EXCEPTIONS;
SELECT * FROM EXCEPTIONS; |
|
Wichtiges zu
Constraints |
|
- Ist ein ON DELETE CASCADE gesetzt, so ist der
Index auf dem Foreign Key wesentlich für die Performance.
- Für
eine optimale Indexierung auf Primary Keys immer die USING INDEX
Clause verwenden.
- Ein CRETAE TABLE AS SELECT * FROM ....
verliert alle Constraints ausser NOT NULL.
- Vorsicht bei DISABLE
CONSTRAINT, da die Indexes gedroppt werden.
- Constraints immer
selber benennen (leichter lesbare
Fehlermeldungen). |
Diverse hilfreiche
Kommandos
|
SELECT name FROM v$database;
SELECT * FROM
v$datafile
SELECT * FROM v$logfile
SELECT * FROM
global_name;
ALTER TABLESPACE tab OFFLINE NORMAL;
ALTER
TABLESPACE tab OFFLINE IMMEDIATE; |
Datenbank Name abfragen.
Welche DB-Files sind
vorhanden
Welche Logfiles sind vorhanden
Domain Namen
abfragen.
Tablespace offline setzen
Tablespace sofort offline
setzen | | 
